package by.scsbsu.database.dao;

import java.sql.SQLException;

import by.scsbsu.database.bean.Task;
import by.scsbsu.database.tools.DbConnectionPool;

public class TaskDAO extends AbstractDAO {
	public static final String SQL_TASK_CREATE_TASK = "INSERT INTO task ( id_study_course , text , title , creation_date , dead_line_date , max_upload_numb , id_mark_policy ) VALUES ( ? , ? , ? , ? , ? , ? , ? );";
	public static final String SQL_TASK_GET_ID_CREATE_TASK = "SELECT id FROM task WHERE ( ( id_study_course = ? ) AND ( id_mark_policy = ? ) AND ( title = ? ) );";
	
	public static final String SQL_TASK_GET_TEXT = "SELECT text FROM task WHERE ( id = ? );";
	public static final String SQL_TASK_GET_TITLE = "SELECT title FROM task WHERE ( id = ? );";
	public static final String SQL_TASK_GET_CREATION_DATE = "SELECT creation_date FROM task WHERE ( id = ? );";
	public static final String SQL_TASK_GET_DEAD_LINE_DATE = "SELECT dead_line_date FROM task WHERE ( id = ? );";
	public static final String SQL_TASK_GET_MAX_UPLOAD_NUMB = "SELECT max_upload_numb FROM task WHERE ( id = ? );";
	
	public static final String SQL_TASK_UPDATE_TEXT = "UPDATE task SET ( text = ? ) WHERE ( id = ? );";
	public static final String SQL_TASK_UPDATE_TITLE = "UPDATE task SET ( title = ? ) WHERE ( id = ? );";
	public static final String SQL_TASK_UPDATE_CREATION_DATE = "UPDATE task SET ( creation_date = ? ) WHERE ( id = ? );";
	public static final String SQL_TASK_UPDATE_DEAD_LINE_DATE = "UPDATE task SET ( dead_line_date = ? ) WHERE ( id = ? );";
	public static final String SQL_TASK_UPDATE_MAX_UPLOAD_NUMB = "UPDATE task SET ( max_upload_numb = ? ) WHERE ( id = ? );";
	
	public static final String SQL_TASK_DELETE_TASK_BY_ID = "DELETE FROM task WHERE ( id = ? );";
	
	public Task createTaskAttach(Task task){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_CREATE_TASK);
			statement.setInt(1, task.getIdStudyCourse());
			statement.setString(2, task.getText());
			statement.setString(3, task.getTitle());
			statement.setLong(4, task.getCreationDate());
			statement.setLong(5, task.getDeadLineDate());
			statement.setInt(6, task.getMaxUploadNumb());
			statement.setInt(7, task.getIdMarkPolicy());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(SQL_TASK_GET_ID_CREATE_TASK);
			statement.setInt(1, task.getIdStudyCourse());
			statement.setInt(2, task.getIdMarkPolicy());
			statement.setString(3, task.getTitle());
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				task.setId(resultSet.getInt("id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
		return task;
	}
	
	public String getTextOfTask(int id){
		String text = new String();
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_GET_TEXT);
			statement.setInt(1, id);
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				text = resultSet.getString("text");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return text;
	}
	
	public String getTitleOfTask(int id){
		String title = new String();
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_GET_TITLE);
			statement.setInt(1, id);
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				title = resultSet.getString("title");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return title;
	}
	
	public Long getCreationDateOfTask(int id){
		Long date = 0L;
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_GET_CREATION_DATE);
			statement.setInt(1, id);
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				date = resultSet.getLong("creation_date");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}
	
	public Long getDeadLineDateOfTask(int id){
		Long date = 0L;
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_GET_DEAD_LINE_DATE);
			statement.setInt(1, id);
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				date = resultSet.getLong("dead_line_date");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}
	
	public int getMaxUploadNumbOfTask(int id){
		int numb = 0;
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_GET_MAX_UPLOAD_NUMB);
			statement.setInt(1, id);
			
			resultSet = statement.executeQuery();
			if(resultSet.next()){
				numb = resultSet.getInt("max_upload_numb");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return numb;
	}
	
	public void updateTextOfTask(int id, String text){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_UPDATE_TEXT);
			statement.setString(1, text);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void updateTitleOfTask(int id, String title){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_UPDATE_TITLE);
			statement.setString(1, title);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void updateCreationDateOfTask(int id, Long date){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_UPDATE_CREATION_DATE);
			statement.setLong(1, date);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void updateDeadLineDateOfTask(int id, Long date){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_UPDATE_DEAD_LINE_DATE);
			statement.setLong(1, date);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void updateMaxUploadNumbOfTask(int id, int numb){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_UPDATE_MAX_UPLOAD_NUMB);
			statement.setLong(1, numb);
			statement.setInt(2, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	
	public void deleteTaskById(int id){
		try {
			connection = DbConnectionPool.getConnection();
			
			statement = connection.prepareStatement(SQL_TASK_DELETE_TASK_BY_ID);
			statement.setInt(1, id);
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
}
